﻿CREATE OR REPLACE VIEW registro.vwhresum AS 
 SELECT a.cidpers, a.crazsoc, a.direccf, a.vtipdoc, a.vnrodoc AS nnrodoc, 
    b.vdirpre, b.ccodpre, b.cperiod, b.vnrodoc, b.dfecdoc, b.vmotivo, b.nporcen, 
    b.ntertot, b.nterren, b.ncomtot, b.narecom, c.nautova, c.nbaseim, c.nimpanu, 
    c.ntotpre, c.nimptri, b.nestado, b.destado, b.mhresum::character(10), b.mobserv, b.nvalida, 
    b.ctippro, b.dpredio, b.mpredio
   FROM vwperson a
   JOIN registro.vwpredio b ON a.cidpers = b.mperson
   LEFT JOIN registro.dresumn c ON b.mhresum::bpchar = c.mhresum AND b.cperiod = c.cperiod;

ALTER TABLE registro.vwhresum
  OWNER TO postgres;
GRANT ALL ON TABLE registro.vwhresum TO postgres;
GRANT SELECT ON TABLE registro.vwhresum TO desarrollo;

CREATE OR REPLACE VIEW registro.vwpredio AS 
 SELECT a.idsigma AS mpropie, b.idsigma AS mpredio, a.ctippro, 
    c.vdescri AS vtippro, j.dfecadq, j.dfecdes, o.vnrodoc, o.dfecdoc, o.cmotivo, 
    d.vdescri AS vmotivo, o.ctipdat, e.vdescri AS vtipfic, o.ctipdoc, 
    e.vdescri AS vtipdoc, g.tnompob, h.tnomvia, b.dnumero, b.dinteri, b.dletras, 
    b.ddepart, b.destaci, b.ddeposi, b.drefere, b.dmanzan, b.dnlotes, b.ccatast, 
    b.cplanos, b.ctipmer, b.dnummer, b.cdiscat, b.czoncat, b.cmzacat, b.cseccat, 
    b.cltecat, b.cundcat, b.dbloque, b.dseccio, b.dunidad, b.vdirpre, b.ccodpre, 
    COALESCE(n.cperiod, to_char(now(), 'yyyy'))::character(4) cperiod, b.idanexo, b.ccodcuc, i.vdescri AS vtippre, j.idsigma AS dpredio, 
    j.ctippre, j.cclasif, j.ccondic, j.cestado, j.cusogen, j.cusoesp, j.nporcen, 
    j.ntertot, j.nporter, j.nterren, j.ncomtot, j.nporcom, j.narecom, n.narance, 
    n.nvalpis, n.nvalins, n.nvalter, n.nvalpre, 
        CASE
            WHEN j.nporafe::integer = 0 THEN 100.00
            ELSE j.nporafe
        END::numeric(10,2) AS nporafe, 
    n.nvalafe, j.dafecta, j.nfrente, j.ncanper, o.vnrodjj, j.ctippar, j.vobserv, 
    o.nestado, a.nestado AS pestado, j.nestado AS destado, b.nestado AS mestado, 
    a.mperson, k.vdescri AS vusogen, l.vdescri AS vusoesp, m.vdescri AS vtippar, 
    j.vusernm, j.ddatetm, j.dfectra, j.cnotari, j.ctiptra, j.cpartid, j.csubtip, 
    j.cmotadq, h.ccodvia, h.mviadis, o.idsigma AS mhresum, o.vobserv AS mobserv, 
    k.viconos, p.vdescri AS vcondic, q.vdescri AS vestado, r.vdescri AS vsubtip, 
    COALESCE(t.vdescri, 'AFECTO'::character varying) AS vregime, 
    u.vobserv AS vclasif, n.nbarrid, n.nparque, n.nrecole, n.nserena, a.nvalida, 
    o.nrecalc
   FROM registro.mhresum o
   JOIN registro.mpropie a ON o.idsigma::bpchar = a.mhresum
   JOIN registro.mpredio b ON a.mpredio = b.idsigma
   JOIN mconten c ON a.ctippro = c.idsigma
   JOIN mconten d ON o.cmotivo = d.idsigma
   LEFT JOIN registro.vwpoblad g ON b.mpoblad = g.mpoblad
   LEFT JOIN registro.vwviadis h ON b.mviadis = h.mviadis
   LEFT JOIN mconten i ON b.ctippre = i.idsigma
   LEFT JOIN registro.dpredio j ON b.idsigma = j.mpredio AND o.idsigma::bpchar = j.mhresum
   LEFT JOIN registro.vpredio n ON j.idsigma::bpchar = n.dpredio
   LEFT JOIN mconten e ON o.ctipdat = e.idsigma
   LEFT JOIN mconten f ON o.ctipdoc = f.idsigma
   LEFT JOIN mconten k ON j.cusogen = k.idsigma
   LEFT JOIN mconten l ON j.cusoesp = l.idsigma
   LEFT JOIN mconten m ON j.ctippar = m.idsigma
   LEFT JOIN mconten p ON j.ccondic = p.idsigma
   LEFT JOIN mconten q ON j.cestado = q.idsigma
   LEFT JOIN mconten r ON j.csubtip::bpchar = r.idsigma
   LEFT JOIN registro.minafec s ON a.mpredio = s.mpredio::bpchar AND a.mperson = s.mperson::bpchar
   LEFT JOIN mconten t ON s.ctipina = t.idsigma
   LEFT JOIN mconten u ON j.cclasif = u.idsigma;

ALTER TABLE registro.vwpredio
  OWNER TO postgres;
GRANT ALL ON TABLE registro.vwpredio TO postgres;
GRANT SELECT ON TABLE registro.vwpredio TO desarrollo;

CREATE OR REPLACE VIEW registro.vwbase_predio as
select
	a.idsigma, /*right(a.ccodpre,10)*/ a.idsigma ccodpre, a.mviadis,
	trim(a.dnumero) dnumero, trim(a.dmanzan) dmanzan, trim(a.dnlotes) dnlotes,
	trim(a.ddepart) ddepart, trim(a.dinteri) dinteri, trim(a.drefere) drefere,
	a.dletras, a.destaci, a.ddeposi, a.ccatast, a.cplanos, a.ctipmer, a.dnummer, a.cdiscat, 
	a.czoncat, a.cmzacat, a.cseccat, a.cltecat, a.cundcat, a.dbloque, a.dseccio, a.dunidad, 
	a.mpoblad, a.nestado, a.ctippre, a.idanexo, a.ccodcuc, a.vhostnm, a.vusernm, a.ddatetm,
	a.nlatitu, a.nlongit, a.nzoom  , b.tnomvia,
	(b.tnomvia
		|| case when length(trim(a.dnumero)) > 0 then ' Nro. ' || trim(a.dnumero) else '' end
		|| case when length(trim(a.ddepart)) > 0 then ' Dpt. ' || trim(a.ddepart) else '' end
		|| case when length(trim(a.dmanzan)) > 0 then ' Mza. ' || trim(a.dmanzan) else '' end
		|| case when length(trim(a.dnlotes)) > 0 then ' Lte. ' || trim(a.dnlotes) else '' end) :: character varying(300) vdirpre,
	e.cidpers, e.crazsoc, e.direccf, e.vtipdoc, e.vnrodoc pnrodoc, f.vdescri vtippro,
	d.idsigma mhresum, d.vnrodoc, coalesce(d.cperfin, to_char(now(), 'yyyy')) cperiod
from  registro.mpredio a
	inner join registro.vwviadis b on a.mviadis=b.mviadis
	left join registro.mpropie c on a.idsigma=c.mpredio and c.nestado=1
	left join registro.mhresum d on c.mhresum=d.idsigma
	left join public.vwperson e on c.mperson=e.cidpers
	left join public.mconten f on c.ctippro=f.idsigma and f.cidtabl='1000000450'
where coalesce(d.cperfin, to_char(now(), 'yyyy'))=to_char(now(), 'yyyy')
	and coalesce(d.nestado, 1)=1
	and coalesce(a.nestado, '1')='1'
	and coalesce(c.ctippro, '1000000451')='1000000451';

CREATE OR REPLACE FUNCTION pl_function.buscar_predio_sin_asignar(p_ccodpre character varying, p_ccodvia character varying, p_cnrovia character varying, p_cmanzan character varying, p_cnrlote character varying, p_ref refcursor)
  RETURNS refcursor AS
$BODY$
begin
	open p_ref for select
		a.idsigma, a.ccodpre, a.mviadis, a.dnumero, a.dmanzan, a.dnlotes,
		a.ddepart, a.dinteri, a.drefere, a.dletras, a.destaci, a.ddeposi,
		a.ccatast, a.cplanos, a.ctipmer, a.dnummer, a.cdiscat, a.czoncat,
		a.cmzacat, a.cseccat, a.cltecat, a.cundcat, a.dbloque, a.dseccio,
		a.dunidad, a.mpoblad, a.nestado, a.ctippre, a.idanexo, a.ccodcuc,
		a.vhostnm, a.vusernm, a.ddatetm, a.nlatitu, a.nlongit, a.nzoom  ,
		a.tnomvia, a.vdirpre, a.cidpers, a.crazsoc, a.direccf, a.vtipdoc,
		a.pnrodoc, a.vtippro, a.mhresum, a.vnrodoc, a.cperiod
	from  registro.vwbase_predio a
	where a.cidpers is null
		and a.idsigma like '%' || p_ccodpre || '%'
		and a.mviadis like '%' || p_ccodvia || '%'
		and a.dnumero like '%' || p_cnrovia || '%'
		and a.dmanzan like '%' || p_cmanzan || '%'
		and a.dnlotes like '%' || p_cnrlote || '%';
	return p_ref;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION pl_function.buscar_predio_sin_asignar(character varying, character varying, character varying, character varying, character varying, refcursor) OWNER TO postgres;

CREATE OR REPLACE FUNCTION pl_function.buscar_predio(p_ccodpre character varying, p_ccodvia character varying, p_cnrovia character varying, p_cmanzan character varying, p_cnrlote character varying, p_ref refcursor)
  RETURNS refcursor AS
$BODY$
begin
	open p_ref for select
		a.idsigma, a.ccodpre, a.mviadis, a.dnumero, a.dmanzan, a.dnlotes,
		a.ddepart, a.dinteri, a.drefere, a.dletras, a.destaci, a.ddeposi,
		a.ccatast, a.cplanos, a.ctipmer, a.dnummer, a.cdiscat, a.czoncat,
		a.cmzacat, a.cseccat, a.cltecat, a.cundcat, a.dbloque, a.dseccio,
		a.dunidad, a.mpoblad, a.nestado, a.ctippre, a.idanexo, a.ccodcuc,
		a.vhostnm, a.vusernm, a.ddatetm, a.nlatitu, a.nlongit, a.nzoom  ,
		a.tnomvia, a.vdirpre, a.cidpers, a.crazsoc, a.direccf, a.vtipdoc,
		a.pnrodoc, a.vtippro, a.mhresum, a.vnrodoc, a.cperiod
	from  registro.vwbase_predio a
	where a.cidpers is not null
		and a.idsigma like '%' || p_ccodpre || '%'
		and a.mviadis like '%' || p_ccodvia || '%'
		and a.dnumero like '%' || p_cnrovia || '%'
		and a.dmanzan like '%' || p_cmanzan || '%'
		and a.dnlotes like '%' || p_cnrlote || '%';
	return p_ref;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION pl_function.buscar_predio(character varying, character varying, character varying, character varying, character varying, character varying, refcursor) OWNER TO postgres;

select grantallobject();
-- select * from registro.mpredio where idsigma not in(select mpredio from registro.mpropie);



